/*
 * Copyright (c) 2006, 2022, webrx.cn All rights reserved.
 *
 */
package cn.webrx;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * <p>Project: jdbc202202 - StmtDemo
 * <p>Powered by webrx On 2022-08-19 17:34:21
 *
 * @author webrx [webrx@126.com]
 * @version 1.0
 * @since 17
 */
public class StmtDemo {
    Connection conn;


    @Test @DisplayName("测试Statement语句CRUD操作")
    void stcrud() throws Exception{
        //建立表
        Statement s = conn.createStatement();
        //s.execute("create table if not exists tt(id int unsigned auto_increment primary key,name varchar(30),birthday date)");

        //(1) insert
        //int rows = s.executeUpdate("insert into tt values(null,'张三丰','2002-3-15'),(null,'李强','2010-08-12')");
        //System.out.println(rows);

        //(2) select
        ResultSet rs = s.executeQuery("select id,name,birthday from tt order by birthday desc");
        while(rs.next()){
            System.out.printf("编号：%03d，姓名：%s，出生日期:%tF%n",rs.getInt("id"),rs.getString(2),rs.getDate(3));
        }

        //(3) delete
        int rows = s.executeUpdate("delete  from tt where id = 1");
        System.out.println(rows);

        //(4) update
        int n = s.executeUpdate("update tt set name = 'jack' where id = 2");
        System.out.println(n);

    }



    @Test
    void dbs() {
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("show databases");
            while (rs.next()) {
                String dbn = rs.getString(1);
                System.out.println(dbn);
                System.out.println("-----------------------------------------------------");
                ResultSet rs2 = conn.createStatement().executeQuery("show tables from "+dbn);
                while(rs2.next()){
                    System.out.printf("%s ",rs2.getString(1));
                }
                System.out.println("\r\n\n");
                rs2.close();
            }
            rs.close();
            st.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Test
    void m() {
        try {
            System.out.println(conn.getMetaData().getDatabaseProductVersion());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @BeforeEach
    void init() {
        try {
            conn = DriverManager.getConnection("jdbc:mysql:/db", "root", "");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @AfterEach
    void close() {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
